How many rows will this query
return ?
select a, b
from (values (1, true),
(2, false),
(3, null)) as t (a,b)
where null;
How many rows will this query
return ?
select a, b
from (values (1, true), (2, false), (3, null)) as t (a,b)
where null;
How many rows will this query
return ?
select a, b
from (values (1, true),
(2, false),
(3, null)) as t (a,b)
where null;
a | b
---+----
(0 rows)
answer is 0 row
null in a where clause is treated like false!
How many rows will this query
return ?
select a, b
from (values (1, 'aa'),
(2, 'bb'),
(3, null)) as t (a,b)
where t.b <> 'aa';
How many rows will this query
return ?
select a, b
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b <> 'aa';
How many rows will this query
return ?
select a, b
from (values (1, 'aa'),
(2, 'bb'),
(3, null)) as t (a,b)
where t.b <> 'aa';
a | b
---+----
2 | bb
(1 row)
answer is 1 row
Because inequality or equality operations are null when
one of the operand is null and null in a where clause
is treated like false!
How many rows will this query
return ?
select a, b
from (values (1, 'aa'),
(2, 'bb'),
(3, null)) as t (a,b)
where t.b is distinct from 'aa';
How many rows will this query
return ?
select a, b
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b is distinct from 'aa';
0
1
2
Nicole-Reine Étable de la Brière
Lepaute
How many rows will this query
return ?
select a, b
from (values (1, 'aa'),
(2, 'bb'),
(3, null)) as t (a,b)
where t.b is distinct from 'aa';
a | b
---+----
2 | bb
3 |
(2 rows)
is distinct means "are not identical".
For SQL standard, identical for null value is :
If V1 and V2 are both the null value, then V1 is
identical to V2.
How many rows will this query
return ?
select a, b
from (values (1, 'aa'),
(2, 'bb'),
(3, null)) as t (a,b)
where t.b in ('aa',null);
How many rows will this query
return ?
select a, b
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b in ('aa',null);
How many rows will this query
return ?
select a, b
from (values (1, 'aa'),
(2, 'bb'),
(3, null)) as t (a,b)
where t.b in ('aa',null);
a | b
---+---
1 | aa
(1 row)
t.b in ('aa',null) is equivalent to t.b = any
('aa',null) As null = null is null and it's in the
where clause, it's considered false
How many rows will this query
return ?
select a, b
from (values (1, 'aa'),
(2, 'bb'),
(3, null)) as t (a,b)
where t.b not in ('aa',null);
How many rows will this query
return ?
select a, b
from (values (1, 'aa'), (2, 'bb'), (3, null)) as t (a,b)
where t.b not in ('aa',null);
How many rows will this query
return ?
select a, b
from (values (1, 'aa'),
(2, 'bb'),
(3, null)) AS t (a,b)
where t.b not in ('aa',null);
a | b
---+---
(0 rows)
t.b not in ('aa',null) is equivalent to not t.b in
('aa',null). So it's equivqlent to 'aa' <> 'aa'
and ... null<> null. Null with and is always null
and null in the where clause is false.
How many rows will this query
return ?
select a, b
from (values (1),
(2),
(null)) as t (a,b)
where t.a between 1 and null;
How many rows will this query
return ?
select a, b
from (values (1), (2), (null)) as t (a,b)
where t.a between 1 and null;
How many rows will this query
return ?
select a, b
from (values (1),
(2),
(null)) as t (a,b)
where t.a between 1 and null;
a | b
---+---
(0 rows)
“X BETWEEN ASYMMETRIC Y AND Z” is equivalent to
“X>=Y AND X<=Z”
How many rows will this query
return?
select a, b
from (values (1, true),
(2, false),
(3, null)) as t (a,b)
where t.b < true;
How many rows will this query
return?
select a, b
from (values (1, true), (2, false), (3, null)) as t (a,b)
where t.b < true;
0
1
2
Commodore Grace M. Hopper
How many rows will this query
return?
select a, b
from (values (1, true),
(2, false),
(3, null)) as t (a,b)
where t.b < true;
a | b
---+---
2 | f
(1 row)
inequality operator + where clause The value True is
greater than the value False, and any comparison
involving the null value or an Unknown truth value will
return an Unknown result.
What will this query return ?
select (null=1)
or (1=1) as "Annie Easley";
What will this query return ?
select (null=1) or (1=1) as "Annie Easley";
true
false
null
Annie Easley
What will this query return ?
select (null=1)
or (1=1) as "Annie Easley";
Annie Easley
--------------
t
(1 row)
What will this query return ?
select null is null is null
is null is null
as "Margaret Hamilton";
What will this query return ?
select null is null is null is null is null as "Margaret
Hamilton";
true
false
null
Margaret Hamilton
What will this query return ?
select null is null is null
is null is null
as "Margaret Hamilton";
Margaret Hamilton
-------------------
f
(1 row)
The first one is true, all the others are false
What will this query return ?
select row(null) is null;
What will this query return ?
select row(null) is null;
true
false
null
Radia Perlman
What will this query return ?
select row(null) is null;
?column?
----------
t
(1 row)
Ok, so for what the SQL Standard calls "null
predicate", we are clearly in the case where :
Let R be the row value predicand and let V be the
value of R.
If the value of every field of V is the null
value, then [the value of “R IS NULL” is] True.
What will this query return ?
select row(row(null)) is null;
What will this query return ?
select row(row(null)) is null;
true
false
null
Brenda Laurel
What will this query return ?
select row(row(null)) is null;
?column?
----------
f
(1 row)
Let R be the row value predicand and let V be the
value of R.
If the value of every field of V is the null
value, then [the value of “R IS NULL” is] True.
But, the value of the first field is not the null
va;lue but row(null), so it's false!
What will this query return ?
select nullif(null,1);
What will this query return ?
select nullif(null,1);
What will this query return ?
select nullif(null,1);
nullif
--------------
Ada Lovelace
(1 row)
Nullif returns true if both value are equals and first
value if not.
What will this query return ?
select 'Mary Lou ' || a || 'Jepsen'
from (values (null)) as t (a);
What will this query return ?
select 'Mary Lou ' || a || 'Jepsen'
from (values (null)) as t (a);
0
unknown
Mary Lou Jepsen
Ada Lovelace
concatenation with null is null
What will this query return ?
select 'Mary Lou ' || a || 'Jepsen'
from (values (null)) as t (a);
?column?
--------------
Ada Lovelace
(1 row)